Create Staging Tables in Staging Database and Populate the Staging Tables - Continued2 10

To Load Stg_StgHiring Table:




Source WorkerDetails:



SQL command Text:


SELECT [Work Order ID], [Current Bill Rate (DT/Hr)], [Current Bill Rate (OT/Hr)], [Current Bill Rate (ST/Hr)]

FROM [dbo].[Worker_Details]

WHERE [Work Order ID] IS NOT NULL


Columns Tab:



Source Timing:





SQL command Text:


select DISTINCT [Job Posting ID],  [# Responses], [# Hired], 

[# Positions Requested],[# Unfilled Positions], [Work Order ID], [Time to Fill]

from [dbo].[Timing]

where [Job Posting ID] is NOT null


Columns Tab:





Source SIH:




SQL command Text:

SELECT DISTINCT [SIH_Requisition ID], [SIH_# Interviewed], [SIH_# Accepted Interviews],[SIH_# Confirmed Interviews] FROM [dbo].[vw_SIH]


Columns Tab:



Sort:




Sort 1:




Sort 2:




Merge Join:




Sort 3:




Merge Join 1:




Derived Columns:





Derived Column Text:


GETDATE()

GETDATE()

ISNULL([Current Bill Rate (DT/Hr)]) ? 0 : [Current Bill Rate (DT/Hr)]

ISNULL([Current Bill Rate (OT/Hr)]) ? 0 : [Current Bill Rate (OT/Hr)]

ISNULL([Current Bill Rate (ST/Hr)]) ? 0 : [Current Bill Rate (ST/Hr)]

ISNULL([Job Posting ID]) ? "Unkown" : [Job Posting ID]

ISNULL([# Responses]) ? 0 : [# Responses]

ISNULL([# Hired]) ? 0 : [# Hired]

ISNULL([# Positions Requested]) ? 0 : [# Positions Requested]

ISNULL([# Unfilled Positions]) ? 0 : [# Unfilled Positions]

ISNULL([Time to Fill]) ? 0 : [Time to Fill]

ISNULL([Work Order ID (1)]) ? "Unkown" : [Work Order ID (1)]

ISNULL([SIH_# Interviewed]) ? 0 : [SIH_# Interviewed]

ISNULL([SIH_# Accepted Interviews]) ? 0 : [SIH_# Accepted Interviews]

ISNULL([SIH_# Confirmed Interviews]) ? 0 : [SIH_# Confirmed Interviews]


OLEDB Destination:






To Load_ReqCategory Table:





Source Vw_ReqA:




SQL command Text:


SELECT [ReqA_Requisition ID], [ReqA_Company Code],[ReqA_Category], [ReqA_Functional Org], [ReqA_Org], [ReqA_Division], 

[ReqA_Subdivision], [ReqA_Level 3], [ReqA_Level 4], [ReqA_Level 5] 

 

FROM [dbo].[vw_ReqA]


Column Tab:



Derived Column:




Derived Column Text:


GETDATE()

GETDATE()
ISNULL([ReqA_Functional Org]) ? "Unkown" : [ReqA_Functional Org]

ISNULL(ReqA_Org) ? "Unknown" : ReqA_Org

ISNULL(ReqA_Division) ? "Unknown" : ReqA_Division

ISNULL(ReqA_Subdivision) ? "Unknown" : ReqA_Subdivision

ISNULL([ReqA_Level 3]) ? "Unknown" : [ReqA_Level 3]

ISNULL([ReqA_Level 4]) ? " Unknown" : [ReqA_Level 4]

ISNULL([ReqA_Level 5]) ? "Unknown" : [ReqA_Level 5]



OLEDB Destination:



Mappings Tab:




To Load_Stg_JobReq Table:








Source VW_ReqA:




Columns Tab:




Derived Column:




OLEDB Destination:




Mappings Tab:





To Load_StgWorker Table:






Source VW_Worker:




SQL command Text:


SELECT  [Worker_Work Order ID], [Worker_Contractor ID], [Worker_Security ID], [Worker_Worker Type], [Worker_Contractor],

[Worker_Original Start Date], [Worker_Contractor Start Date], [Worker_Contractor End Date], [Worker_Contractor Closed Date], 

   [Worker_Contractor Status], [Worker_Contractor Type], [Worker_Main Document Type], [Worker_Nuetral Supplier Name], 

   [Worker_Diverse Supplier],   [Worker_Supervisor],[Worker_Primary Company Code - Cost Center],[Worker_Template Title],[Worker_Country],

 [Worker_City], [Worker_Site], [Worker_Address], [Worker_State/Province], [Worker_Uploaded?]


 FROM [RecSta].[dbo].[vw_Worker]

where [Worker_Work Order ID] is not null


Columns Tab:






Data Conversion:




Derived Columns:




Derived Column Text:


GETDATE()

GETDATE()

ISNULL([Worker_Work Order ID]) ? "Unknown" : [Worker_Work Order ID]

ISNULL([Worker_Contractor ID]) ? "Unknown" : [Worker_Contractor ID]

ISNULL([Worker_Security ID]) ? "Unknown" : [Worker_Security ID]

ISNULL([Worker_Worker Type]) ? "Unknown" : [Worker_Worker Type]

ISNULL(Worker_Contractor) ? "Unknown" : Worker_Contractor

ISNULL([Worker_Contractor Status]) ? "Unknown" : [Worker_Contractor Status]

ISNULL([Worker_Contractor Type]) ? "Unknown" : [Worker_Contractor Type]

ISNULL([Worker_Main Document Type]) ? "Unknown" : [Worker_Main Document Type]

ISNULL([Worker_Nuetral Supplier Name]) ? "Unknown" : [Worker_Nuetral Supplier Name]

ISNULL([Worker_Diverse Supplier]) ? "Unknown" : [Worker_Diverse Supplier]

ISNULL(Worker_Supervisor) ? "Unknown" : Worker_Supervisor

ISNULL([Worker_Primary Company Code - Cost Center]) ? "" : [Worker_Primary Company Code - Cost Center]

ISNULL([Worker_Template Title]) ? "Unknown" : [Worker_Template Title]

ISNULL(Worker_City) ? "Unknown" : Worker_City

ISNULL(Worker_Country) ? "Unknown" : Worker_Country

ISNULL(Worker_Site) ? "Unknown" : Worker_Site

ISNULL([Worker_State/Province]) ? "Unknown" : [Worker_State/Province]

ISNULL([Worker_Uploaded?]) ? "Unknown" : [Worker_Uploaded?]

ISNULL(Worker_Address) ? "Unknown" : Worker_Address

ISNULL([Copy of Worker_Original Start Date]) ? "Unknown" : [Copy of Worker_Original Start Date]

ISNULL([Copy of Worker_Contractor Start Date]) ? "Unknown" : [Copy of Worker_Contractor Start Date]

ISNULL([Copy of Worker_Contractor End Date]) ? "Unknown" : [Copy of Worker_Contractor End Date]

ISNULL([Copy of Worker_Contractor Closed Date]) ? "Unknown" : [Copy of Worker_Contractor Closed Date]


Conditional split:




Conditional Split Text:


[Worker_State/Province] != "% %" || Worker_Address != "% %" || [Worker_Worker Type] != "% %"


Derived Columns:





Union All:





OLEDB Destination:




Mappings Tab:






Union All Transformation

https://www.tutorialgateway.org/union-all-transformation-in-ssis/